﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using cecbData;
using cecbClasses.Data_Classes;
using cecbClasses;
using System.Data;

namespace cecbClasses.Data_Adapters.Project
{
    public class Func_Projects
    {
        public Func_Projects()
        {
            dataContext = new DSSEntities();
        }

        DSSEntities dataContext;


        public Boolean updateProjects(Data_Projects arg)
        {
            Boolean isUpdated = false;

            try
            {
                Projects pro = new Projects();
                pro.proj_approvingAgency = arg.Proj_approvingAgency;
                pro.proj_category = arg.Proj_category;
                pro.proj_consultant = arg.Proj_consultant;
                pro.proj_decisionComment = arg.Proj_decisionComment;
                pro.proj_decisonUpDate = arg.Proj_decisonUpDate;
                pro.proj_finalDecision = arg.Proj_finalDecision;
                pro.proj_finalResult = arg.Proj_finalResult;
                pro.proj_fromDate = arg.Proj_fromDate;
                pro.proj_location = arg.Proj_location;
                pro.proj_modelImage = arg.Proj_modelImage;
                pro.proj_name = arg.Proj_name;
                pro.proj_otherDetails = arg.Proj_otherDetails;
                pro.proj_planImage = arg.Proj_planImage;
                pro.proj_proponent = arg.Proj_proponent;
                pro.proj_reference = arg.Proj_reference;
                pro.proj_status = arg.Proj_status;
                pro.proj_toDate = arg.Proj_toDate;
                pro.proj_type = arg.Proj_type;

                dataContext.AddToProjects(pro);
                dataContext.SaveChanges();

                isUpdated = true;
            }
            catch (Exception)
            {

                throw;
            }

            return isUpdated;
        }


        //public Boolean insertIntoProjects(Data_Projects arg)
        //{
        //    Boolean isInserted = false;

        //    try
        //    {
        //        Projects pro = new Projects();
        //        pro.proj_approvingAgency = arg.Proj_approvingAgency;
        //        pro.proj_category = arg.Proj_category;
        //        pro.proj_consultant = arg.Proj_consultant;
        //        pro.proj_decisionComment = arg.Proj_decisionComment;
        //        pro.proj_decisonUpDate = arg.Proj_decisonUpDate;
        //        pro.proj_finalDecision = arg.Proj_finalDecision;
        //        pro.proj_finalResult = arg.Proj_finalResult;
        //        pro.proj_fromDate = arg.Proj_fromDate;
        //        pro.proj_location = arg.Proj_location;
        //        pro.proj_modelImage = arg.Proj_modelImage;
        //        pro.proj_name = arg.Proj_name;
        //        pro.proj_otherDetails = arg.Proj_otherDetails;
        //        pro.proj_planImage = arg.Proj_planImage;
        //        pro.proj_proponent = arg.Proj_proponent;
        //        pro.proj_reference = arg.Proj_reference;
        //        pro.proj_status = arg.Proj_status;
        //        pro.proj_toDate = arg.Proj_toDate;
        //        pro.proj_type = arg.Proj_type;

        //        dataContext.AddToProjects(pro);
        //        dataContext.SaveChanges();

        //        isInserted = true;
        //    }
        //    catch (Exception)
        //    {

        //        throw;
        //    }

        //    return isInserted;
        //}

        public List<Projects> getProjectsByName(String name)
        {
            IQueryable<Projects> Query =
                  from c in dataContext.Projects
                  where c.proj_name.Contains(name)
                  select c;

            List<Projects> SelectedPrjects = Query.ToList();

            return SelectedPrjects;
        }

        public List<Projects> getAllProjects()
        {
            IQueryable<Projects> Query =
                  from c in dataContext.Projects

                  select c;

            List<Projects> SelectedPrjects = Query.ToList();

            return SelectedPrjects;
        }



        public List<Projects> getAllProjectNames()
        {
            IQueryable<Projects> Query =
            from c in dataContext.Projects
            select c;

            List<Projects> AllfNames = Query.ToList();

            return AllfNames;
        }

        #region  ------------projectGridLocations----------------

        public Boolean insertIntoProjectGridLocation(Data_ProjectGridLocation arg, string prjRef)
        {

            Boolean isInserted = false;

            try
            {
                Project_AreaGrids tbgrids = new Project_AreaGrids();
                tbgrids.proj_reference = prjRef; // arg.Proj_reference;
                tbgrids.loc_longitude = Convert.ToDecimal(arg.Loc_longitude);
                tbgrids.loc_latitude = Convert.ToDecimal(arg.Loc_latitude);
                isInserted = true;

                dataContext.AddToProject_AreaGrids(tbgrids);
                dataContext.SaveChanges();



            }

            catch (Exception)
            {

                throw;
            }

            return isInserted;
        }

        public Boolean UpdateGridLocations(Data_ProjectGridLocation arg)
        {
            bool isUpdated = false;

            try
            {

                var selGridId = from p in dataContext.Project_AreaGrids
                                where p.grid_id == arg.Grid_id
                                select p;

                if (selGridId.Count() > 0)
                {
                    Project_AreaGrids tblPrjctAGrids = selGridId.Single();
                    tblPrjctAGrids.grid_id = selGridId.Single().grid_id;

                    //tblPrjctAGrids.loc_latitude = arg.Loc_latitude;
                    //tblPrjctAGrids.loc_longitude=arg.Loc_longitude'



                    dataContext.SaveChanges();
                    isUpdated = true;
                }

            }

            catch (Exception)
            {

                throw;
            }

            return isUpdated;
        }



        public Boolean DeleteGridLocations(Data_ProjectGridLocation arg)
        {
            bool isDeleted = false;

            try
            {
                var selGridId = from p in dataContext.Project_AreaGrids
                                where p.grid_id == arg.Grid_id
                                select p;
                if (selGridId.Count() > 0)
                {
                    Project_AreaGrids oProject = selGridId.Single();
                    oProject.grid_id = selGridId.Single().grid_id;


                    dataContext.SaveChanges();

                }

                isDeleted = true;
            }

            catch (Exception ex)
            {
                // System.Windows.Forms.MessageBox.Show(ex.ToString());
            }

            return isDeleted;
        }



        public List<Project_AreaGrids> GetGridDtlsByRef(string ProjectCode)
        {
            var query = from c in dataContext.Project_AreaGrids
                        where c.proj_reference == ProjectCode
                        select c;

            return query.ToList();
        }










        #endregion
        #region  "---- Projects ----"
        public Boolean insertIntoProjects(Data_Projects arg, List<Data_ProjectGridLocation> listProj, List<Data_ProjectSubParameter> listProjSubParam)
        {
            Boolean isInserted = false;

            try
            {
                int NextNo = 1;
                string strPrjRef;

                IQueryable<Projects> Query =
                    from t in dataContext.Projects

                    select t;

                if (Query.Count() > 0)
                {
                    NextNo = Query.Count() + 2;
                }


                strPrjRef = "PROJ" + ("00000" + Convert.ToString(NextNo)).Substring(5);

                // var  maxNo = from t in
                Projects tbProj = new Projects();
                tbProj.proj_reference = strPrjRef; // arg.Proj_reference;
                tbProj.proj_name = arg.Proj_name;
                tbProj.proj_category = arg.Proj_category;
                tbProj.proj_type = arg.Proj_type;
                tbProj.proj_location = arg.Proj_location;
                tbProj.proj_proponent = arg.Proj_proponent;
                tbProj.proj_consultant = arg.Proj_consultant;
                tbProj.proj_approvingAgency = arg.Proj_approvingAgency;
                tbProj.proj_modelImage = arg.Proj_modelImage;
                tbProj.proj_planImage = arg.Proj_planImage;
                tbProj.proj_otherDetails = arg.Proj_otherDetails;
                tbProj.proj_status = arg.Proj_status;

                dataContext.AddToProjects(tbProj);
                dataContext.SaveChanges();

                foreach (Data_ProjectGridLocation ObjGrdLoc in listProj)
                {

                    if ((ObjGrdLoc.Loc_latitude != null) && (ObjGrdLoc.Loc_latitude != ""))
                    {
                        Project.Func_Projects dg = new Project.Func_Projects();
                        dg.insertIntoProjectGridLocation(ObjGrdLoc, strPrjRef);
                    }
                }

                // --- Save Project Sub Parameters ---
                foreach (Data_ProjectSubParameter sPara in listProjSubParam)
                {
                    if ((sPara.SubPrm_ref != null) && (sPara.SubPrm_value != ""))
                    {
                        projType_paramVal pVale = new projType_paramVal();
                        pVale.mparam_reference = sPara.Mparam_reference;
                        pVale.proj_reference = strPrjRef;
                        pVale.ptype_reference = sPara.Ptype_reference;
                        pVale.subPrm_ref = sPara.SubPrm_ref;
                        pVale.subPrm_value = sPara.SubPrm_value;

                        dataContext.projType_paramVal.AddObject(pVale);
                    }
                }

                dataContext.SaveChanges();
                isInserted = true;
            }


            catch (Exception)
            {

                throw;
            }

            return isInserted;
        }




        public List<Projects> GetAllRegProjRefernces()
        {
            IQueryable<Projects> Query =
                     from t in dataContext.Projects
                     where t.proj_finalDecision == null && t.proj_status == true
                     select t;

            List<Projects> proj_reference = Query.ToList();

            Projects oPrj = new Projects();
            oPrj.proj_reference = "";
            oPrj.proj_name = "Select a Project";

            proj_reference.Insert(0, oPrj);

            return proj_reference;
        }





        public List<Projects> GetAllRegProjNames()
        {
            IQueryable<Projects> Query =
                     from t in dataContext.Projects
                     where t.proj_finalDecision == null && t.proj_status == true
                     select t;

            List<Projects> proj_name = Query.ToList();

            return proj_name;
        }




        public Projects GetProjectByCode(Data_Projects arg)
        {
            var query = (from p in dataContext.Projects
                         where p.proj_reference == arg.Proj_reference
                         select p);



            if (query.Count() > 0)
            {
                return query.Single();
            }
            else
            {
                Projects tPrj = new Projects();
                tPrj.proj_reference = "-1";
                return tPrj;
            }
        }




        public Projects GetProjectByName(Data_Projects arg)
        {
            var query = (from p in dataContext.Projects
                         where p.proj_name == arg.Proj_name
                         select p);


            if (query.Count() > 0)
            {
                return query.Single();
            }
            else
            {
                Projects tPrj = new Projects();
                tPrj.proj_reference = "";
                return tPrj;
            }


        }


        public List<Data_ProjectSubParameter> GetSubParameterList(string mProjRef)
        {

            List<Data_ProjectSubParameter> listParm = new List<Data_ProjectSubParameter>();


            var query = (from s in dataContext.ProjTySubParams
                         from p in dataContext.projType_paramVal
                                     .Where(x => s.subPrm_ref == x.subPrm_ref && x.proj_reference == mProjRef)
                                     .DefaultIfEmpty()
                         join m in dataContext.ProjTyMParams on s.mparam_reference equals m.mparam_reference
                         select new
                         {
                             p.proj_reference,
                             p.ptype_reference,
                             s.subPrm_ref
                           ,
                             p.subPrm_value,
                             s.subPrm_name,
                             s.mparam_reference,
                             m.mparam_name
                         }).ToList();


            ////var query = (from s in dataContext.ProjTySubParams
            ////            from p in dataContext.projType_paramVal 
            ////                        .Where(x=> s.subPrm_ref == x.subPrm_ref && x.proj_reference == mProjRef)
            ////                        .DefaultIfEmpty()
            ////            join m in dataContext.ProjTyMParams on s.mparam_reference equals m.mparam_reference 
            ////            where (from t in dataContext.projType_paramVal
            ////                   where t.proj_reference == mProjRef
            ////                   select p.mparam_reference).Contains(m.mparam_name)
            ////            select new  { p.proj_reference, p.ptype_reference, s.subPrm_ref
            ////                          , p.subPrm_value, s.subPrm_name, s.mparam_reference,m.mparam_name }).ToList();


            //var filterList = (from a in query
            //                  where (from p in dataContext.projType_paramVal
            //                         where p.proj_reference == mProjRef
            //                         select p.mparam_reference).Contains(a.mparam_name)
            //                  select a).ToList();

            ////var query = (from s in dataContext.ProjTySubParams
            ////             from p in dataContext.projType_paramVal
            ////                         .Where(x => s.subPrm_ref == x.subPrm_ref && x.proj_reference == mProjRef)
            ////                         .DefaultIfEmpty()

            ////             from m in
            ////                 dataContext.ProjTyMParams
            ////                   .Where(y => y.mparam_reference == s.mparam_reference && (from r in dataContext.projType_paramVal
            ////                                                                            where r.proj_reference == mProjRef
            ////                                                                            select r.mparam_reference).Contains(y.mparam_reference))
            ////                   .DefaultIfEmpty()
            ////             select new
            ////             {
            ////                 p.proj_reference,
            ////                 p.ptype_reference,
            ////                 s.subPrm_ref ,
            ////                 p.subPrm_value,
            ////                 s.subPrm_name,
            ////                 s.mparam_reference,
            ////                 m.mparam_name
            ////             });


            //oSubParamList = (from t in oSubParamList
            //                 where (SelectedMainParamList.Any(mRef => mRef.mparam_name == t.Mparam_name))
            //                 select t).ToList();


            foreach (var p in query)
            {

                var isExists = (from pr in dataContext.projType_paramVal
                                where pr.proj_reference == mProjRef && pr.mparam_reference == p.mparam_reference
                                select pr).Count();

                if (isExists > 0)
                {
                    Data_ProjectSubParameter pSubParam = new Data_ProjectSubParameter();

                    pSubParam.Proj_reference = p.proj_reference;
                    pSubParam.Ptype_reference = p.ptype_reference;
                    pSubParam.SubPrm_ref = p.subPrm_ref;
                    pSubParam.SubPrm_name = p.subPrm_name;
                    pSubParam.SubPrm_value = p.subPrm_value;
                    pSubParam.Mparam_reference = p.mparam_reference;
                    pSubParam.Mparam_name = p.mparam_name;

                    listParm.Add(pSubParam);
                }
            }

            return listParm;


        }

        public Boolean UpdateProjects(Data_Projects arg)
        {
            bool isUpdated = false;

            try
            {

                var selProjRef = from p in dataContext.Projects
                                 where p.proj_reference == arg.Proj_reference && p.proj_status == true
                                 select p;

                if (selProjRef.Count() > 0)
                {
                    Projects tblPrjct = selProjRef.Single();
                    tblPrjct.proj_reference = selProjRef.Single().proj_reference;

                    tblPrjct.proj_name = arg.Proj_name;
                    tblPrjct.proj_location = arg.Proj_location;
                    tblPrjct.proj_type = arg.Proj_type;
                    tblPrjct.proj_category = arg.Proj_category;
                    tblPrjct.proj_proponent = arg.Proj_proponent;
                    tblPrjct.proj_consultant = arg.Proj_consultant;
                    tblPrjct.proj_approvingAgency = arg.Proj_approvingAgency;
                    tblPrjct.proj_modelImage = arg.Proj_modelImage;
                    tblPrjct.proj_planImage = arg.Proj_planImage;
                    tblPrjct.proj_otherDetails = arg.Proj_otherDetails;
                    tblPrjct.proj_status = arg.Proj_status;


                    dataContext.SaveChanges();
                    isUpdated = true;
                }

            }

            catch (Exception)
            {

                throw;
            }

            return isUpdated;
        }

        public Boolean DeleteProjects(Data_Projects arg)
        {
            bool isDeleted = false;

            try
            {
                var SelePrject = from p in dataContext.Projects
                                 where p.proj_reference == arg.Proj_reference
                                 select p;
                if (SelePrject.Count() > 0)
                {
                    Projects oProject = SelePrject.Single();
                    oProject.proj_id = SelePrject.Single().proj_id;
                    oProject.proj_status = false;

                    dataContext.SaveChanges();
                }

                isDeleted = true;
            }

            catch (Exception ex)
            {
                // System.Windows.Forms.MessageBox.Show(ex.ToString());
            }

            return isDeleted;
        }
        #endregion




        #region "------Project_Types---------"
        public Boolean insertIntoProjectTypes(Data_ProjectTypes arg)
        {
            Boolean isInserted = false;

            try
            {

                int NextNo = 1;
                string strPrjType;

                IQueryable<ProjectTypes> Query =
                    from t in dataContext.ProjectTypes

                    select t;

                if (Query.Count() > 0)
                {
                    NextNo = Query.Count() + 1;
                }


                strPrjType = "PTYP" + ("00000" + Convert.ToString(NextNo)).Substring(5);


                ProjectTypes tb = new ProjectTypes();
                tb.ptype_reference = strPrjType;
                tb.ptype_name = arg.Ptype_name;
                tb.ptype_status = true;

                dataContext.AddToProjectTypes(tb);
                dataContext.SaveChanges();

                isInserted = true;



            }

            catch (Exception)
            {

                throw;
            }

            return isInserted;
        }



        public List<ProjectTypes> GetAllProjectTypes()
        {
            IQueryable<ProjectTypes> Query =
                     from t in dataContext.ProjectTypes
                     where t.ptype_status == true
                     select t;

            List<ProjectTypes> ptype_name = Query.ToList();

            return ptype_name;
        }

        public ProjectTypes GetProjectByTypeCode(Data_ProjectTypes arg)
        {
            var query = (from p in dataContext.ProjectTypes
                         where p.ptype_reference == arg.Ptype_reference && p.ptype_status == true
                         select p);



            if (query.Count() > 0)
            {
                return query.Single();
            }
            else
            {
                ProjectTypes tPrjType = new ProjectTypes();
                tPrjType.ptype_reference = "-1";
                return tPrjType;
            }
        }

        public ProjectTypes GetProjectByTypeName(Data_ProjectTypes arg)
        {
            var query = (from p in dataContext.ProjectTypes
                         where p.ptype_name == arg.Ptype_name && p.ptype_status == true
                         select p);



            if (query.Count() > 0)
            {
                return query.First();
            }
            else
            {
                ProjectTypes tPrjType = new ProjectTypes();
                tPrjType.ptype_reference = "-1";
                return tPrjType;
            }
        }

        public Boolean UpdateProjTypes(Data_ProjectTypes arg)
        {
            bool isUpdated = false;

            try
            {

                var selProjTypeRef = from p in dataContext.ProjectTypes
                                     where p.ptype_reference == arg.Ptype_reference && p.ptype_status == true
                                     select p;

                ProjectTypes tblProjType = selProjTypeRef.Single();
                tblProjType.ptype_reference = selProjTypeRef.Single().ptype_reference;

                tblProjType.ptype_name = arg.Ptype_name;
                dataContext.SaveChanges();
                isUpdated = true;


            }

            catch (Exception)
            {

                throw;
            }

            return isUpdated;
        }

        public Boolean DeleteProjectTypes(Data_ProjectTypes arg)
        {
            bool isDeleted = false;

            try
            {
                var SelePrject = from p in dataContext.ProjectTypes
                                 where p.ptype_reference == arg.Ptype_reference
                                 select p;
                if (SelePrject.Count() > 0)
                {
                    ProjectTypes oProjectType = SelePrject.Single();
                    oProjectType.ptype_reference = SelePrject.Single().ptype_reference;
                    oProjectType.ptype_status = false;

                    dataContext.SaveChanges();
                }

                isDeleted = true;
            }

            catch (Exception ex)
            {
                //System.Windows.Forms.MessageBox.Show(ex.ToString());
            }

            return isDeleted;
        }

        #endregion

        #region  "------  Project Main Param -------- "
        public Boolean insertIntoMainParam(string ParamName , string PrjTypeRef)//(Data_PTypeMainParam arg, Data_ProjectTypes arg1)
        {

            Boolean isInserted = false;

            try
            {

                int NextNo = 1;
                string strMainParam;

                IQueryable<ProjTyMParams> Query =
                    from t in dataContext.ProjTyMParams

                    select t;

                if (Query.Count() > 0)
                {
                    NextNo = Query.Count() + 1;
                }


                strMainParam = "MPRM" + ("00000" + Convert.ToString(NextNo)).Substring(5);


                ProjTyMParams tbMParam = new ProjTyMParams();
                tbMParam.mparam_reference = strMainParam;
                tbMParam.mparam_name = ParamName;// arg.Pmparam_Name;
                tbMParam.ptype_reference = PrjTypeRef;//arg1.Ptype_reference;

                dataContext.AddToProjTyMParams(tbMParam);
                dataContext.SaveChanges();

                isInserted = true;



            }

            catch (Exception)
            {

                throw;
            }

            return isInserted;
        }


        public List<ProjTyMParams> getAllMainParamerter()
        {
            IQueryable<ProjTyMParams> Query =
           from c in dataContext.ProjTyMParams
           select c;

            List<ProjTyMParams> AllMParam = Query.ToList();

            return AllMParam;
        }

        #endregion

        #region "------ Sub Parameters ------"
        public Boolean insertIntoSubPrm(string  SubParamName, string MParamRef)//(Data_ProjSubParameters arg, Data_PTypeMainParam arg1)
        {

            Boolean isInserted = false;

            try
            {

                int NextNo = 1;
                string strSubParam;

                IQueryable<ProjTySubParams> Query =
                    from t in dataContext.ProjTySubParams

                    select t;

                if (Query.Count() > 0)
                {
                    NextNo = Query.Count() + 1;
                }


                strSubParam = "SPRM" + ("00000" + Convert.ToString(NextNo)).Substring(5);


                ProjTySubParams tbSParm = new ProjTySubParams();
                tbSParm.subPrm_ref = strSubParam; //arg.SubPrm_ref;
                tbSParm.subPrm_name = SubParamName;// arg.SubPrm_name;
                tbSParm.mparam_reference = MParamRef; // arg1.Pmparam_Name;

                dataContext.AddToProjTySubParams(tbSParm);
                dataContext.SaveChanges();

                isInserted = true;



            }

            catch (Exception)
            {

                throw;
            }

            return isInserted;
        }

        public List<ProjTySubParams> getAllSubParamerter(string mParamRef)
        {
            IQueryable<ProjTySubParams> Query =
           from c in dataContext.ProjTySubParams
           where c.mparam_reference == mParamRef
           select c;

            List<ProjTySubParams> AllSubParam = Query.ToList();

            return AllSubParam;
        }

        #endregion

        #region ---------ProjectCatogries----------
        public Boolean insertIntoCategories(Data_Categories arg)
        {
            Boolean isInserted = false;

            try
            {
                int NextNo = 1;
                string strPrjCatRef;

                IQueryable<Categories> Query =
                    from t in dataContext.Categories

                    select t;

                if (Query.Count() > 0)
                {
                    NextNo = Query.Count() + 1;
                }


                strPrjCatRef = "PCAT" + ("00000" + Convert.ToString(NextNo)).Substring(5);


                Categories tbcat = new Categories();
                tbcat.cat_reference = strPrjCatRef;
                tbcat.cat_name = arg.Cat_name;
                tbcat.cat_status = true;

                dataContext.AddToCategories(tbcat);
                dataContext.SaveChanges();

                isInserted = true;
            }
            catch (Exception)
            {

                throw;
            }

            return isInserted;
        }

        public List<Categories> GetAllCatogries()
        {
            IQueryable<Categories> Query =
                from w in dataContext.Categories
                where w.cat_status == true
                select w;
            List<Categories> cat_name = Query.ToList();
            return cat_name;


        }

        public List<Categories> GetAllCatref()
        {
            IQueryable<Categories> Query =
                from w in dataContext.Categories
                select w;
            List<Categories> cat_reference = Query.ToList();
            return cat_reference;


        }

        public Categories GetProjectBycatCode(Data_Categories arg)
        {
            var query = (from p in dataContext.Categories
                         where p.cat_reference == arg.Cat_reference
                         select p);



            if (query.Count() > 0)
            {
                return query.Single();
            }
            else
            {
                Categories tPrj = new Categories();
                tPrj.cat_reference = "-1";
                return tPrj;
            }
        }

        public Categories GetProjectBycatName(Data_Categories arg)
        {
            var query = (from p in dataContext.Categories
                         where p.cat_name == arg.Cat_name
                         select p);



            if (query.Count() > 0)
            {
                return query.Single();
            }
            else
            {
                Categories tPrj = new Categories();
                tPrj.cat_reference = "-1";
                return tPrj;
            }
        }

        public Boolean UpdateProjCatgries(Data_Categories arg)
        {
            bool isUpdated = false;

            try
            {

                var selCatRef = from p in dataContext.Categories
                                where p.cat_reference == arg.Cat_reference && p.cat_status == true
                                select p;

                Categories tblCatgries = selCatRef.Single();
                tblCatgries.cat_reference = selCatRef.Single().cat_reference;

                tblCatgries.cat_name = arg.Cat_name;
                dataContext.SaveChanges();
                isUpdated = true;


            }

            catch (Exception)
            {

                throw;
            }

            return isUpdated;
        }
        public Boolean DeleteProjects(Data_Categories arg)
        {
            bool isDeleted = false;

            try
            {
                var SelePrject = from p in dataContext.Categories
                                 where p.cat_reference == arg.Cat_reference
                                 select p;
                if (SelePrject.Count() > 0)
                {
                    Categories oProject = SelePrject.Single();
                    oProject.cat_reference = SelePrject.Single().cat_reference;
                    oProject.cat_status = false;

                    dataContext.SaveChanges();
                }

                isDeleted = true;
            }

            catch (Exception ex)
            {
                //System.Windows.Forms.MessageBox.Show(ex.ToString());
            }

            return isDeleted;
        }

        #endregion


        #region"----------EIADetails--------------------"


        public Boolean InsertEIADetails(Data_Projects arg)
        {
            bool isUpdated = false;

            try
            {

                var selProjRef = from p in dataContext.Projects
                                 where p.proj_reference == arg.Proj_reference && p.proj_status == true
                                 select p;


                Projects tblPrjct = selProjRef.Single();
                tblPrjct.proj_reference = selProjRef.Single().proj_reference;

                tblPrjct.proj_fromDate = arg.Proj_fromDate;
                tblPrjct.proj_toDate = arg.Proj_toDate;
                tblPrjct.proj_finalDecision = arg.Proj_finalDecision;

                dataContext.SaveChanges();

                isUpdated = true;

            }



            catch (Exception)
            {

                throw;
            }

            return isUpdated;


        }


        public Boolean InsertMetrixCriterias(Data_ProjectCriterias arg)
        {
            bool isInserted = false;

            try
            {
                ProjectCriteria tblProjCrit = new ProjectCriteria();

                tblProjCrit.proj_reference = arg.Proj_reference;
                tblProjCrit.proj_matrix = arg.Proj_matrix;
                tblProjCrit.crt_type = arg.Crt_type;
                tblProjCrit.crt_range = arg.Crt_range;

                dataContext.AddToProjectCriteria(tblProjCrit);
                dataContext.SaveChanges();

                isInserted = true;

            }

            catch (Exception e)
            {

                throw;
            }

            return isInserted;
        }


        public Boolean InsertImportenceCriterias(Data_ImportCriterias arg)
        {
            bool isInserted = false;

            try
            {
                ProjectCriteria tblProjCrit = new ProjectCriteria();

                tblProjCrit.proj_reference = arg.Proj_reference;
                tblProjCrit.proj_matrix = arg.Proj_matrix;
                tblProjCrit.crt_type = arg.Crt_type;
                tblProjCrit.crt_range = arg.Crt_range;

                dataContext.AddToProjectCriteria(tblProjCrit);
                dataContext.SaveChanges();

                isInserted = true;

            }



            catch (Exception e)
            {

                throw;
            }

            return isInserted;
        }

        public List<Projects> GetAllPendingProjRefernces()
        {
            IQueryable<Projects> Query =
                     from t in dataContext.Projects
                     where t.proj_finalDecision == "pending" && t.proj_status == true
                     select t;

            List<Projects> proj_reference = Query.ToList();

            return proj_reference;
        }


        public List<Projects> GetAllPendingProjNames()
        {
            IQueryable<Projects> Query =
                     from t in dataContext.Projects
                     where t.proj_finalDecision == "pending" && t.proj_status == true
                     select t;

            List<Projects> proj_name = Query.ToList();

            return proj_name;
        }
        public Projects GetPendingProjectDetailByCode(Data_Projects arg)
        {
            var query = (from p in dataContext.Projects
                         where p.proj_reference == arg.Proj_reference
                         select p);


            if (query.Count() > 0)
            {
                return query.Single();
            }
            else
            {
                Projects tPrj = new Projects();
                tPrj.proj_reference = "-1";
                return tPrj;
            }
        }

        public Projects GetPendingProjectDetailByName(Data_Projects arg)
        {
            var query = (from p in dataContext.Projects
                         where p.proj_name == arg.Proj_name && p.proj_finalDecision == "pending" && p.proj_status == true
                         select p);


            if (query.Count() > 0)
            {
                return query.First();
            }
            else
            {
                Projects tPrj = new Projects();
                tPrj.proj_reference = " ";
                return tPrj;
            }
        }



        public Boolean UpdateEIADetails(Data_Projects arg)
        {
            bool isUpdated = false;

            try
            {

                var selProjRef = from p in dataContext.Projects
                                 where p.proj_reference == arg.Proj_reference
                                 select p;

                if (selProjRef.Count() > 0)
                {
                    Projects tblPrjct = selProjRef.Single();
                    tblPrjct.proj_reference = selProjRef.Single().proj_reference;



                    tblPrjct.proj_fromDate = arg.Proj_fromDate;
                    tblPrjct.proj_toDate = arg.Proj_toDate;


                    dataContext.SaveChanges();
                    isUpdated = true;
                }

            }

            catch (Exception)
            {

                throw;
            }

            return isUpdated;
        }

        public Boolean UpdateMagnitudeMetrixDetails(Data_ProjectCriterias arg)
        {
            bool isUpdated = false;

            try
            {

                var selProjRef = from p in dataContext.ProjectCriteria
                                 where p.proj_reference == arg.Proj_reference && p.proj_matrix == "Leopold" && p.crt_type == "Magnitude"
                                 select p;

                if (selProjRef.Count() > 0)
                {
                    ProjectCriteria tblPrjct = selProjRef.Single();
                    tblPrjct.proj_reference = selProjRef.Single().proj_reference;
                    tblPrjct.crt_range = arg.Crt_range;



                    dataContext.SaveChanges();
                    isUpdated = true;
                }

            }

            catch (Exception ex)
            {

                //System.Windows.Forms.MessageBox.Show(ex.ToString());
            }

            return isUpdated;
        }

        public Boolean UpdateImpotenceMetrixDetails(Data_ImportCriterias arg)
        {
            bool isUpdated = false;

            try
            {

                var selProjRef = from p in dataContext.ProjectCriteria
                                 where p.proj_reference == arg.Proj_reference && p.proj_matrix == "Leopold" && p.crt_type == "Importence"
                                 select p;

                if (selProjRef.Count() > 0)
                {
                    ProjectCriteria tblPrjct = selProjRef.Single();
                    tblPrjct.proj_reference = selProjRef.Single().proj_reference;



                    tblPrjct.crt_range = arg.Crt_range;



                    dataContext.SaveChanges();
                    isUpdated = true;
                }

            }

            catch (Exception)
            {

                throw;
            }

            return isUpdated;
        }



        public Boolean UpdateProjects(Data_Projects arg, List<Data_ProjectGridLocation> listProj, List<Data_ProjectSubParameter> listProjSubParam)
        {
            bool isUpdated = false;

            try
            {

                var selProjRef = from p in dataContext.Projects
                                 where p.proj_reference == arg.Proj_reference && p.proj_status == true
                                 select p;

                if (selProjRef.Count() > 0)
                {
                    Projects tblPrjct = selProjRef.Single();
                    tblPrjct.proj_reference = selProjRef.Single().proj_reference;

                    tblPrjct.proj_name = arg.Proj_name;
                    tblPrjct.proj_location = arg.Proj_location;
                    tblPrjct.proj_type = arg.Proj_type;
                    tblPrjct.proj_category = arg.Proj_category;
                    tblPrjct.proj_proponent = arg.Proj_proponent;
                    tblPrjct.proj_consultant = arg.Proj_consultant;
                    tblPrjct.proj_approvingAgency = arg.Proj_approvingAgency;
                    tblPrjct.proj_modelImage = arg.Proj_modelImage;
                    tblPrjct.proj_planImage = arg.Proj_planImage;
                    tblPrjct.proj_otherDetails = arg.Proj_otherDetails;
                    tblPrjct.proj_status = arg.Proj_status;




                    dataContext.SaveChanges();

                    string projref = arg.Proj_reference;

                    // Delete Grid Details

                    Func_Projects deldg = new Func_Projects();
                    if (deldg.DeleteGridLocations(projref) == true)
                    {


                        // Insert  Grid Loaction
                        foreach (Data_ProjectGridLocation ObjGrdLoc in listProj)
                        {
                            Func_Projects dg = new Func_Projects();
                            dg.insertIntoProjectGridLocation(ObjGrdLoc, projref);

                        }

                    }


                    if (deldg.DeleteProjSubParam(projref) == true)
                    {
                        // --- Save Project Sub Parameters ---
                        //List<Data_ProjectSubParameter> lstParam = (from s in listProjSubParam 
                        //                                           where (s.SubPrm_value != null) || (s.SubPrm_value.Trim()!="")
                        //                                           select s).ToList();

                        List<Data_ProjectSubParameter> lstParam = (from s in listProjSubParam
                                                                   where (s.SubPrm_value != null)
                                                                   select s).ToList();



                        foreach (Data_ProjectSubParameter sPara in lstParam)
                        {


                            if ((sPara.SubPrm_ref != null) && ((sPara.SubPrm_value != "") || (sPara.SubPrm_value != null)))
                            {
                                projType_paramVal pVale = new projType_paramVal();

                                pVale.mparam_reference = sPara.Mparam_reference;
                                pVale.proj_reference = selProjRef.Single().proj_reference;
                                pVale.ptype_reference = arg.Proj_type; //sPara.Ptype_reference;
                                pVale.subPrm_ref = sPara.SubPrm_ref;
                                pVale.subPrm_value = sPara.SubPrm_value;

                                dataContext.projType_paramVal.AddObject(pVale);
                            }
                        }
                    }

                    dataContext.SaveChanges();

                    isUpdated = true;
                }

            }

            catch (Exception ex)
            {

                throw ex;
            }

            return isUpdated;
        }

        public Boolean DeleteGridLocations(string mProjRef)
        {

            bool isDeleted = false;
            try
            {
                var DelRec = from g in dataContext.Project_AreaGrids
                             where g.proj_reference == mProjRef
                             select g;

                foreach (Project_AreaGrids d in DelRec)
                {
                    dataContext.Project_AreaGrids.DeleteObject(d);


                }
                dataContext.SaveChanges();

                isDeleted = true;
            }
            catch
            {
                throw;
            }


            return isDeleted;


        }


        public Boolean DeleteProjSubParam(string mProjRef)
        {

            bool isDeleted = false;
            try
            {
                var DelRec = from g in dataContext.projType_paramVal
                             where g.proj_reference == mProjRef
                             select g;

                foreach (projType_paramVal d in DelRec)
                {
                    dataContext.projType_paramVal.DeleteObject(d);
                }
                dataContext.SaveChanges();

                isDeleted = true;
            }
            catch
            {
                throw;
            }
            return isDeleted;
        }

        public Boolean UpdateSimpleMetrixDetails(Data_ProjectCriterias arg)
        {
            bool isUpdated = false;

            try
            {

                var selProjRef = from p in dataContext.ProjectCriteria
                                 where p.proj_reference == arg.Proj_reference && p.proj_matrix == "Simple" && p.crt_type == "Simple"
                                 select p;

                if (selProjRef.Count() > 0)
                {
                    ProjectCriteria tblPrjct = selProjRef.Single();
                    tblPrjct.proj_reference = selProjRef.Single().proj_reference;
                    tblPrjct.crt_range = arg.Crt_range;



                    dataContext.SaveChanges();
                    isUpdated = true;
                }

            }

            catch (Exception)
            {

                throw;
            }

            return isUpdated;
        }

        public List<ProjectCriteria> GetPendingProjMetrixDetailByCode(Data_ProjectCriterias arg)
        {
            var query = (from p in dataContext.ProjectCriteria
                         where p.proj_reference == arg.Proj_reference
                         select p);

            return query.ToList();
        }

        #endregion


        #region "Leopold Matrix View"

        public List<Get_Leopold_Phase_Result> GetLeopoldPhases(string mProjectRef)
        {
            List<Get_Leopold_Phase_Result> Query =
                    (from t in dataContext.Get_Leopold_Phase(mProjectRef)
                     select t).ToList();

            List<Get_Leopold_Phase_Result> ptype_name = Query.ToList();

            return ptype_name;
        }

        public List<Get_Leopold_Activities_Result> GetLeopoldActivities(string mProjectRef)
        {
            List<Get_Leopold_Activities_Result> Query =
                    (from t in dataContext.Get_Leopold_Activities(mProjectRef)
                     select t).ToList();

            List<Get_Leopold_Activities_Result> ptype_name = Query.ToList();

            return ptype_name;
        }

        public List<Get_Leopold_Impacts_Result> GetLeopolImpacts(string mProjectRef)
        {
            List<Get_Leopold_Impacts_Result> Query =
                    (from t in dataContext.Get_Leopold_Impacts(mProjectRef)
                     select t).ToList();

            List<Get_Leopold_Impacts_Result> ptype_name = Query.ToList();

            return ptype_name;
        }

        public List<Get_Leopold_Matric_Result> GetLeopolMatrices(string mProjectRef)
        {
            List<Get_Leopold_Matric_Result> Query =
                    (from t in dataContext.Get_Leopold_Matric(mProjectRef)
                     select t).ToList();

            List<Get_Leopold_Matric_Result> ptype_name = Query.ToList();

            return ptype_name;
        }

        #endregion



    }
}
